//#define DEBUG_SQL
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.IO;
using System.Linq;

namespace CSW.Framework.Common.Data
{
    /// <summary>
    /// Data Helper.
    /// </summary>
    public static class DataHelper
    {
        /// <summary>
        /// Gets a <see cref="DataTable"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <returns>The <see cref="DataTable"/>.</returns>
        public static DataTable GetDataTable(SqlConnection connection, string sql)
        {
            return GetDataTable(connection, sql, null);
        }

        /// <summary>
        /// Gets a <see cref="DataTable"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <param name="p">The <see cref="SqlParameter"/> array.</param>
        /// <returns>The <see cref="DataTable"/>.</returns>
        public static DataTable GetDataTable(DbConnection connection, string sql, DbParameter[] p)
        {
            Guard.ArgumentNotNull(connection, "connection");
            Guard.ArgumentNotNullOrEmptyString(sql, "sql");

            if (connection is SqlConnection)
                return GetDataTable((SqlConnection)connection, sql, p.Cast<SqlParameter>().ToArray());
            else if (connection is SqlCeConnection)
                return SqlCeDataHelper.GetDataTable((SqlCeConnection)connection, sql, p.Cast<SqlCeParameter>().ToArray());
            else
                throw new Exception(string.Format("Unhandled connection type '{0}'", connection.GetType()));
        }

        /// <summary>
        /// Gets a <see cref="DataTable"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <param name="p">The <see cref="SqlParameter"/> array.</param>
        /// <returns>The <see cref="DataTable"/>.</returns>
        public static DataTable GetDataTable(SqlConnection connection, string sql, SqlParameter[] p)
        {
            Guard.ArgumentNotNull(connection, "connection");
            Guard.ArgumentNotNullOrEmptyString(sql, "sql");

#if DEBUG_SQL
            Debug.WriteLine("");
            Debug.WriteLine(sql);
            Debug.WriteLine("");
#endif

            if (connection.State != ConnectionState.Open)
                connection.Open();

            using (SqlCommand cmd = new SqlCommand(sql, connection))
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                if (connection.ConnectionTimeout > 30)
                    cmd.CommandTimeout = connection.ConnectionTimeout;
                if (p != null)
                    cmd.Parameters.AddRange(p);
                DataSet ds = new DataSet();
                da.Fill(ds);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// Gets a <see cref="DataRow"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <returns>The <see cref="DataRow"/>.</returns>
        public static DataRow GetDataRow(DbConnection connection, string sql)
        {
            Guard.ArgumentNotNull(connection, "connection");
            Guard.ArgumentNotNullOrEmptyString(sql, "sql");

            if (connection is SqlConnection)
                return GetDataRow((SqlConnection)connection, sql);
            else if (connection is SqlCeConnection)
                return SqlCeDataHelper.GetDataRow((SqlCeConnection)connection, sql);
            else
                throw new Exception(string.Format("Unhandled connection type '{0}'", connection.GetType()));
        }

        /// <summary>
        /// Gets a <see cref="DataRow"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <param name="p">The <see cref="SqlParameter"/> array.</param>
        /// <returns>The <see cref="DataRow"/>.</returns>
        public static DataRow GetDataRow(DbConnection connection, string sql, DbParameter[] p)
        {
            Guard.ArgumentNotNull(connection, "connection");
            Guard.ArgumentNotNullOrEmptyString(sql, "sql");

            if (connection is SqlConnection)
                return GetDataRow((SqlConnection)connection, sql, p.Cast<SqlParameter>().ToArray());
            else if (connection is SqlCeConnection)
                return SqlCeDataHelper.GetDataRow((SqlCeConnection)connection, sql, p.Cast<SqlCeParameter>().ToArray());
            else
                throw new Exception(string.Format("Unhandled connection type '{0}'", connection.GetType()));
        }

        /// <summary>
        /// Gets a <see cref="DataRow"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <returns>The <see cref="DataRow"/>.</returns>
        public static DataRow GetDataRow(SqlConnection connection, string sql)
        {
            return GetDataRow(connection, sql, null);
        }

        /// <summary>
        /// Gets a <see cref="DataRow"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <param name="p">The <see cref="SqlParameter"/> array.</param>
        /// <returns>The <see cref="DataRow"/>.</returns>
        public static DataRow GetDataRow(SqlConnection connection, string sql, SqlParameter[] p)
        {
            DataTable dt = GetDataTable(connection, sql, p);
            if (dt.Rows.Count != 1)
                throw new Exception(string.Format("Query returned {0} rows, expected 1.", dt.Rows.Count));

            return dt.Rows[0];
        }

        /// <summary>
        /// Executes a SQL statement.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        public static void ExecuteSQL(SqlConnection connection, string sql)
        {
            ExecuteSQL(connection, sql, null);
        }

        /// <summary>
        /// Executes a SQL statement.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <param name="p">The <see cref="SqlParameter"/> array.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteSQL(SqlConnection connection, string sql, SqlParameter[] p)
        {
            Guard.ArgumentNotNull(connection, "connection");
            Guard.ArgumentNotNullOrEmptyString(sql, "sql");

#if DEBUG_SQL
            Debug.WriteLine("");
            Debug.WriteLine(sql);
            Debug.WriteLine("");
#endif

            if (connection.State != ConnectionState.Open)
                connection.Open();

            using (SqlCommand cmd = new SqlCommand(sql, connection))
            {
                if (connection.ConnectionTimeout > 30)
                    cmd.CommandTimeout = connection.ConnectionTimeout;
                if (p != null)
                    cmd.Parameters.AddRange(p);
                int rowCount = cmd.ExecuteNonQuery();
                
                return rowCount;
            }
        }

        /// <summary>
        /// Combines two data tables.
        /// </summary>
        /// <param name="dt1">The first DataTable.</param>
        /// <param name="dt2">The second DataTable.</param>
        /// <returns>Combined DataTable.</returns>
        public static DataTable CombineDataTables(DataTable dt1, DataTable dt2)
        {
            if (dt1 == null || dt2 == null)
            {
                if (dt1 != null)
                    return dt1.Copy();
                else if (dt2 != null)
                    return dt2.Copy();
                else
                    return null;
            }
            else
            {
                DataTable dt;

                // Iterate through the rows and call ImportRow from the table with the 
                // least number of rows.

                if (dt1.Rows.Count > dt2.Rows.Count)
                {
                    dt = dt1.Copy();
                    foreach (DataRow dr in dt2.Rows)
                        dt.ImportRow(dr);
                }
                else
                {
                    dt = dt2.Copy();
                    foreach (DataRow dr in dt1.Rows)
                        dt.ImportRow(dr);
                }

                return dt;
            }
        }

        /// <summary>
        /// Export a DataTable to a CSV file.
        /// </summary>
        /// <param name="dataTable">DataTable to export</param>
        /// <param name="path">Path to save CSV file</param>
        /// <param name="useQuotes">Use quotes around field values</param>
        /// <param name="includeHeaders">Include column headers in CSV file</param>
        public static void CreateCSV(DataTable dataTable, string path, bool useQuotes, bool includeHeaders)
        {
            CreateCSV(dataTable, path, useQuotes, includeHeaders, null);
        }

        /// <summary>
        /// Export a DataTable to a CSV file.
        /// </summary>
        /// <param name="dataTable">DataTable to export</param>
        /// <param name="path">Path to save CSV file</param>
        /// <param name="useQuotes">Use quotes around field values</param>
        /// <param name="includeHeaders">Include column headers in CSV file</param>
        /// <param name="columnNames">The column names to export. (null = all columns).</param>
        public static bool CreateCSV(DataTable dataTable, string path, bool useQuotes, bool includeHeaders, ICollection<string> columnNames)
        {
            Guard.ArgumentNotNull(dataTable, "dataTable");
            Guard.ArgumentNotNullOrEmptyString(path, "path");

            if (columnNames == null || columnNames.Count == 0)
            {
                columnNames = new List<string>();
                foreach (DataColumn dataColumn in dataTable.Columns)
                    columnNames.Add(dataColumn.ColumnName);
            }

            Directory.CreateDirectory(Path.GetDirectoryName(path));
            using (StreamWriter streamWriter = new StreamWriter(path))
            {
                bool isFirstColumn;
                string quote = "";

                if (useQuotes)
                    quote = "\"";

                if (includeHeaders)
                {
                    isFirstColumn = true;
                    foreach (string columnName in columnNames)
                    {
                        string value = dataTable.Columns[columnName].ColumnName;
                        if (!useQuotes)
                            value = value.Replace(",", "");

                        streamWriter.Write((!isFirstColumn ? "," : "") + quote + value + quote);
                        isFirstColumn = false;
                    }
                    streamWriter.WriteLine();
                }

                foreach (DataRow dr in dataTable.Rows)
                {
                    isFirstColumn = true;
                    foreach (string columnName in columnNames)
                    {
                        string value = dr[columnName].ToString().Replace(quote, quote + quote);
                        if (!useQuotes)
                            value = value.Replace(",", "");

                        streamWriter.Write((!isFirstColumn ? "," : "") + quote + value + quote);
                        isFirstColumn = false;
                    }
                    streamWriter.WriteLine();
                }
            }

            return true;
        }

        /// <summary>
        /// Gets the <see cref="DbParameter"/> based on the connection type.
        /// </summary>
        /// <param name="dataProvider">The data provider.</param>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <param name="sqlDbType">The SQL type.</param>
        /// <param name="value">The value.</param>
        /// <returns>The <see cref="DbParameter"/>.</returns>
        public static DbParameter GetDbParameter(string dataProvider, string parameterName, SqlDbType sqlDbType, object value)
        {
            Guard.ArgumentNotNullOrEmptyString(dataProvider, "dataProvider");
            Guard.ArgumentNotNullOrEmptyString(parameterName, "parameterName");

            if (dataProvider == "SQL Server")
            {
                SqlParameter p = new SqlParameter(parameterName, sqlDbType);
                p.Value = value;
                return p;
            }
            else if (dataProvider == "SQL Server CE")
            {
                SqlCeParameter p = new SqlCeParameter(parameterName, sqlDbType);
                p.Value = value;
                return p;
            }
            else
            {
                throw new Exception(string.Format("Unhandled connection type '{0}'", dataProvider));
            }

        }

        /// <summary>
        /// Gets the <see cref="DbParameter"/> based on the connection type.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="parameterName">Name of the parameter.</param>
        /// <param name="sqlDbType">The SQL type.</param>
        /// <param name="value">The value.</param>
        /// <returns>The <see cref="DbParameter"/>.</returns>
        public static DbParameter GetDbParameter(DbConnection connection, string parameterName, SqlDbType sqlDbType, object value)
        {
            Guard.ArgumentNotNull(connection, "connection");
            Guard.ArgumentNotNullOrEmptyString(parameterName, "parameterName");

            if (connection is SqlConnection)
                return GetDbParameter("SQL Server", parameterName, sqlDbType, value);
            else if (connection is SqlCeConnection)
                return GetDbParameter("SQL Server CE", parameterName, sqlDbType, value);
            else
                throw new Exception(string.Format("Unhandled connection type '{0}'", connection.GetType()));
        }
    }
}